package exttable.testselector;

import dbutils.DbUtils;
import dbutils.StoredQueries;
import dbutils.StringQueries;
import exttable.SGTTestParametrs;
import exttable.view.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.Vector;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: May 4, 2010
 * Time: 5:05:06 PM
 * To change this template use File | Settings | File Templates.
 */
public class QueryHandler {

    public static Vector<ViewType> getTypes() throws Exception {
        try {
            Vector<ViewType> toReturn = new Vector<ViewType>();
            Connection c = DbUtils.getDBConnectionGeneral();
//            System.out.println("closed: " + c.isClosed());
            PreparedStatement ps = c.prepareStatement(StringQueries.TEST_TYPES);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewType vt = new ViewType.Build()
                        .type(rs.getString("id"))
                        .nameRu(rs.getString("name_ru"))
                        .nameEn(rs.getString("name_en"))
                        .nameKz(rs.getString("name_kk"))
                        .build();
                toReturn.add(vt);
            }
            ps.close();
            c.close();
            return toReturn;
        } catch (Exception e) {
            throw new Exception("no connection");
        }
    }

    public static Vector<ViewGroup> getProbationGroupList(String analysType) throws Exception {
        Vector<ViewGroup> toReturn = new Vector<ViewGroup>();
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement(StoredQueries.getQuery(StoredQueries.GROUPS_LIST));
        ps.setString(1, analysType);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            ViewGroup vg = new ViewGroup.Build()
                    .groupId(rs.getInt("groupId"))
                    .nameRu(rs.getString("name_ru"))
                    .nameEn(rs.getString("name_en"))
                    .nameKz(rs.getString("name_kk"))
                    .analysType(analysType).build();
            toReturn.add(vg);
        }
        ps.close();
        c.close();
        return toReturn;
    }

    public static int getGroupSchemaId(int groupId, String analysType) throws Exception {
        int toReturn = 0;
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StoredQueries.getQuery(StoredQueries.SCHEMA_GROUP_ID));
            ps.setInt(1, groupId);
            ps.setString(2, analysType);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn = rs.getInt(1);
            }
            ps.close();
            c.close();
            return toReturn;
        } catch (Exception e) {
            throw new Exception("no connection");
        }
    }

    public static Vector<ViewProbationer> getProbationList(int groupCode) throws Exception {
        try {
            Vector<ViewProbationer> toReturn = new Vector<ViewProbationer>();
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.PROBATIONER);
            ps.setInt(1, groupCode);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewProbationer vp = new ViewProbationer.Build().probationerId(rs.getInt("probationerId"))
                        .probationCode(rs.getString("probationCode"))
                        .vizitZero(rs.getDate("vizitZero"))
                        .build();
                toReturn.add(vp);
            }
            ps.close();
            c.close();
            return toReturn;
        } catch (Exception e) {
            throw new Exception("no connection");
        }
    }

    public static Vector<ViewProbationer> getProbationList(String analysisType, String phaseId, int schemaID) throws Exception {
        try {
            Vector<ViewProbationer> toReturn = new Vector<ViewProbationer>();
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.PROBATIONER_BY_PHASE);
            ps.setString(1, analysisType);
            ps.setString(2, phaseId);
            ps.setInt(3, schemaID);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewProbationer vp = new ViewProbationer.Build().probationerId(rs.getInt("probationerId"))
                        .probationCode(rs.getString("probationCode"))
                        .vizitZero(rs.getDate("vizitZero"))
                        .build();
                toReturn.add(vp);
            }
            ps.close();
            c.close();
            return toReturn;
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }

    public static Vector<ViewProbationer> getProbationList(String analysisType, String phase) throws Exception {
        try {
            Vector<ViewProbationer> toReturn = new Vector<ViewProbationer>();
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.PROBATIONER_BY_PHASE_TYPE);
            ps.setString(1, phase);
            ps.setString(2, analysisType);


            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewProbationer vp = new ViewProbationer.Build().probationerId(rs.getInt("probationerId"))
                        .probationCode(rs.getString("probationCode"))
                        .vizitZero(rs.getDate("vizitZero"))
                        .groupId(rs.getInt("group_id"))
                        .build();
                toReturn.add(vp);
            }
            ps.close();
            c.close();
            return toReturn;
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }





    public static Vector<ViewPhases> getPhasesList(String analysisTye) throws Exception {
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement(StringQueries.PHASES);
        try {
            Vector<ViewPhases> toReturn = new Vector<ViewPhases>();
            ps.setString(1, analysisTye);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewPhases vp = new ViewPhases();
                vp.setPhaseId(rs.getString("dp.phase_id"));
                vp.setNameRu(rs.getString("name_ru"));
                vp.setNameEn(rs.getString("name_en"));
                vp.setNameKz(rs.getString("name_kk"));
                toReturn.add(vp);
            }
            ps.close();
            c.close();
            return toReturn;

        } catch (Exception e) {
            throw new Exception("no connection");
        } finally {
            ps.close();
            c.close();
        }

    }

    public static Collection<? extends ViewSchema> getSchemaList(int probID, String testType) throws Exception {

        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement(StringQueries.SCHEMA_BY_PROBATIONER);

        try {
            Vector<ViewSchema> toReturn = new Vector<ViewSchema>();
            ps.setInt(1, probID);
            ps.setString(2, testType);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                ViewSchema vs = new ViewSchema();
                vs.setSchemaId(rs.getInt("gsv.groups_schema_visits_id"));
                vs.setSchemaName(rs.getString(SGTTestParametrs.getOurInstance().getCurrentLocal().getColumn()));
                vs.setNameRu(rs.getString("name_ru"));
                vs.setNameEn(rs.getString("name_en"));
                vs.setNameKz(rs.getString("name_kk"));
                toReturn.add(vs);
            }
            ps.close();
            c.close();
            return toReturn;

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            ps.close();
            c.close();
        }
    }
}
